iT邦幫忙

第 11 屆 iThome 鐵人賽
DAY 7
0
自我挑戰組

MySQL 學習筆記系列 第 7 篇

create view

11th鐵人賽
1039 瀏覽

  • 分享至 

  • xImage
    •  

前言

  • 如果有一段較長的 SQL 敘述常常使用，我們可以使用 create view ，將較長的一般 SQL 敘述存起來，之後再使用它會比較方便。

一般 SQL 敘述

MariaDB [class]> select cid,cname,ch,ebg,math,ch+ebg+math sum from bk1;
+-----+--------+------+-----+------+------+
| cid | cname  | ch   | ebg | math | sum  |
+-----+--------+------+-----+------+------+
|  01 | 簡奉君 |   13 |  59 |    0 |   72 |
|  02 | 黃靖輪 |  100 |  32 |  100 |  232 |
|  03 | 潘四敬 |    5 |  48 |  100 |  153 |
|  04 | 賴勝恩 |   63 |  52 |  100 |  215 |
|  05 | 黎楚寧 |    4 |   6 |  100 |  110 |
|  06 | 蔡中穎 |   70 |  95 |  100 |  265 |
|  07 | 徐佳螢 |   28 |  56 |  100 |  184 |
|  08 | 林雨媗 |   96 |   0 |  100 |  196 |
|  09 | 林心儀 |   79 |  45 |  100 |  224 |
|  10 | 王燕博 |   15 |   4 |  100 |  119 |
|  11 | Brad   |   99 |  88 |   33 |  220 |
+-----+--------+------+-----+------+------+
11 rows in set (0.059 sec)

MariaDB [class]> create view scoreview as
    -> select cid,cname,ch,ebg,math,ch+ebg+math sum from bk1;
Query OK, 0 rows affected (0.019 sec)

MariaDB [class]> select * from scoreview;
+-----+--------+------+-----+------+------+
| cid | cname  | ch   | ebg | math | sum  |
+-----+--------+------+-----+------+------+
|  01 | 簡奉君 |   13 |  59 |    0 |   72 |
|  02 | 黃靖輪 |  100 |  32 |  100 |  232 |
|  03 | 潘四敬 |    5 |  48 |  100 |  153 |
|  04 | 賴勝恩 |   63 |  52 |  100 |  215 |
|  05 | 黎楚寧 |    4 |   6 |  100 |  110 |
|  06 | 蔡中穎 |   70 |  95 |  100 |  265 |
|  07 | 徐佳螢 |   28 |  56 |  100 |  184 |
|  08 | 林雨媗 |   96 |   0 |  100 |  196 |
|  09 | 林心儀 |   79 |  45 |  100 |  224 |
|  10 | 王燕博 |   15 |   4 |  100 |  119 |
|  11 | Brad   |   99 |  88 |   33 |  220 |
+-----+--------+------+-----+------+------+
11 rows in set (0.001 sec)

修改一位同學的分數為100分在bk1中

MariaDB [class]> update  bk1 set math=100 where cid=1;
Query OK, 1 row affected (0.051 sec)
Rows matched: 1  Changed: 1  Warnings: 0


//查看scoreview是否也修改?
//查看後，資料也被修改了
MariaDB [class]> select * from scoreview;
+-----+--------+------+-----+------+------+
| cid | cname  | ch   | ebg | math | sum  |
+-----+--------+------+-----+------+------+
|  01 | 簡奉君 |   13 |  59 |  100 |  172 |
|  02 | 黃靖輪 |  100 |  32 |  100 |  232 |
|  03 | 潘四敬 |    5 |  48 |  100 |  153 |
|  04 | 賴勝恩 |   63 |  52 |  100 |  215 |
|  05 | 黎楚寧 |    4 |   6 |  100 |  110 |
|  06 | 蔡中穎 |   70 |  95 |  100 |  265 |
|  07 | 徐佳螢 |   28 |  56 |  100 |  184 |
|  08 | 林雨媗 |   96 |   0 |  100 |  196 |
|  09 | 林心儀 |   79 |  45 |  100 |  224 |
|  10 | 王燕博 |   15 |   4 |  100 |  119 |
|  11 | Brad   |   99 |  88 |   33 |  220 |
+-----+--------+------+-----+------+------+
11 rows in set (0.001 sec)

若是從 view中去修成績，table裡面也會一併被修改

MariaDB [class]> update scoreview set math=0 where cid=1;

MariaDB [class]>  select * from bk1\G;
*************************** 1. row ***************************
      cID: 01
    cName: 簡奉君
     cSex: F
cBirthday: 1987-04-04
   cEmail: elven@superstar.com
   cPhone: 0922988876
    cAddr: 台北市濟洲北路12號
       ch: 13
      ebg: 59
     math: 0
*************************** 2. row ***************************
      cID: 02
    cName: 黃靖輪
     cSex: M
cBirthday: 1987-07-01
   cEmail: jinglun@superstar.com
   cPhone: 0918181111
    cAddr: 台北市敦化南路93號5樓
       ch: 100
      ebg: 32
     math: 100
*************************** 3. row ***************************

.......省略

註:一般不會從view去修改值

在bk1新增一位同學，資料也會顯示在view裡面

MariaDB [class]> insert into bk1 values (11,'Brad','M','1999-01-02','','','',10,20,30);
Query OK, 1 row affected (0.013 sec)

MariaDB [class]> select * from scoreview;
+-----+--------+------+-----+------+------+
| cid | cname  | ch   | ebg | math | sum  |
+-----+--------+------+-----+------+------+
|  01 | 簡奉君 |   13 |  59 |    0 |   72 |
|  02 | 黃靖輪 |  100 |  32 |  100 |  232 |
|  03 | 潘四敬 |    5 |  48 |  100 |  153 |
|  04 | 賴勝恩 |   63 |  52 |  100 |  215 |
|  05 | 黎楚寧 |    4 |   6 |  100 |  110 |
|  06 | 蔡中穎 |   70 |  95 |  100 |  265 |
|  07 | 徐佳螢 |   28 |  56 |  100 |  184 |
|  08 | 林雨媗 |   96 |   0 |  100 |  196 |
|  09 | 林心儀 |   79 |  45 |  100 |  224 |
|  10 | 王燕博 |   15 |   4 |  100 |  119 |
|  11 | Brad   |   10 |  20 |   30 |   60 |
+-----+--------+------+-----+------+------+
12 rows in set (0.001 sec)

在view裡面新增學生名字就會有錯誤產生，所以鮮少在view裡面做增刪修的動作(切記)，因為view可能是合併出來的一張表，原資料有些欄位並沒有

MariaDB [class]> inster into scoreview values (12,'tony',1,2,3);
ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MariaDB server version for the right syntax to use near 'inster into scoreview values (12,'tony',1,2,3)' at line 1
MariaDB [class]>

搭配 join 使用

tb21表中有的欄位tb1f1是要跟tb1做關聯性的

  • 清空裡面所有tables

  • 分別在重新建立tb1，tb2兩個資料表

MariaDB [db1]> create table tb2 (id int,f2 varchar(10), tbf1 varchar(10));

MariaDB [db1]> desc tb2;
+------- +-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+----- --+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| f2     | varchar(10) | YES  |     | NULL    |       |
| tb1f1  | varchar(10) | YES  |     | NULL    |       |
+---- ---+-------------+------+-----+---------+-------+
3 rows in set (0.022 sec)


MariaDB [db1]> insert into tb1 values (1,'A'),(2,'B'),(3,'C'),(4,'D');
Query OK, 4 rows affected (0.050 sec)
Records: 4  Duplicates: 0  Warnings: 0

MariaDB [db1]> select * from tb1;
+------+------+
| id   | f1   |
+------+------+
|    1 | A    |
|    2 | B    |
|    3 | C    |
|    4 | D    |
+------+------+
4 rows in set (0.000 sec)

寫入tb2欄位資料

MariaDB [db1]> insert into tb2 value (1,'AA','A');
Query OK, 1 row affected (0.050 sec)

MariaDB [db1]> insert into tb2 value (2,'BB','B');
Query OK, 1 row affected (0.051 sec)

MariaDB [db1]> insert into tb2 value (3,'CC','Z');
Query OK, 1 row affected (0.050 sec)

MariaDB [db1]> select * from tb2;
+------+------+------+
| id   | f2   | tbf1 |
+------+------+------+
|    1 | AA   | A    |
|    2 | BB   | B    |
|    3 | CC   | Z    |
+------+------+------+

join

  • 首先複習一下 join 的用法
MariaDB [db1]> select tb1.id,tb1.f1,tb2.f2 from tb1
    -> join tb2 on (tb1.f1 = tb2.tbf1);
+------+------+------+
| id   | f1   | f2   |
+------+------+------+
|    1 | A    | AA   |
|    2 | B    | BB   |
+------+------+------+
2 rows in set (0.001 sec)

左邊join

MariaDB [db1]> select tb1.id,tb1.f1,tb2.f2 from tb1
    -> left join tb2 on (tb1.f1 = tb2.tbf1);
+------+------+------+
| id   | f1   | f2   |
+------+------+------+
|    1 | A    | AA   |
|    2 | B    | BB   |
|    3 | C    | NULL |
|    4 | D    | NULL |
+------+------+------+
4 rows in set (0.001 sec)

右邊join

MariaDB [db1]> select tb1.id,tb1.f1,tb2.f2 from tb1
    -> right join tb2 on (tb1.f1 = tb2.tbf1);
+------+------+------+
| id   | f1   | f2   |
+------+------+------+
|    1 | A    | AA   |
|    2 | B    | BB   |
| NULL | NULL | CC   |
+------+------+------+
3 rows in set (0.000 sec)

創建 create view的練習

  1. 首先先建立一個結果
MariaDB [db1]> select tb1.id,tb1.f1,tb2.f2 from tb1
    -> join tb2 on (tb1.f1 = tb2.tbf1);
+------+------+------+
| id   | f1   | f2   |
+------+------+------+
|    1 | A    | AA   |
|    2 | B    | BB   |
+------+------+------+
2 rows in set (0.001 sec)
  1. 然後，創建一個view，在as 之後加入你想要的結果(同上)
MariaDB [db1]> create view myview as
    -> select tb1.id,tb1.f1,tb2.f2 from tb1
    -> join tb2 on (tb1.f1 = tb2.tbf1);
Query OK, 0 rows affected (0.020 sec)

MariaDB [db1]> show tables;//資料庫中多了一個view
+---------------+
| Tables_in_db1 |
+---------------+
| myview        |
| tb1           |
| tb2           |
+---------------+
3 rows in set (0.001 sec)

MariaDB [db1]> desc myview;//此view的欄位
+-------+-------------+------+-----+---------+-------+
| Field | Type        | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id    | int(11)     | YES  |     | NULL    |       |
| f1    | varchar(10) | YES  |     | NULL    |       |
| f2    | varchar(10) | YES  |     | NULL    |       |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.024 sec)

MariaDB [db1]> select * from myview;//view內容同上述的1.
+------+------+------+
| id   | f1   | f2   |
+------+------+------+
|    1 | A    | AA   |
|    2 | B    | BB   |
+------+------+------+
2 rows in set (0.001 sec)
  1. 倘若我把tb2的欄位裡面的值修改
MariaDB [db1]> update tb2 set tbf1 ='C' where id=3;
Query OK, 1 row affected (0.014 sec)
Rows matched: 1  Changed: 1  Warnings: 0

MariaDB [db1]> select * from tb2;
+------+------+------+
| id   | f2   | tbf1 |
+------+------+------+
|    1 | AA   | A    |
|    2 | BB   | B    |
|    3 | CC   | C    |
+------+------+------+
3 rows in set (0.000 sec)
  1. 由於tb2有修改，所以myview也被修改了
MariaDB [db1]> select * from myview;
+------+------+------+
| id   | f1   | f2   |
+------+------+------+
|    1 | A    | AA   |
|    2 | B    | BB   |
|    3 | C    | CC   |
+------+------+------+
3 rows in set (0.000 sec)

上一篇
運用 where... in...和 join
下一篇
北風資料庫情境模擬
系列文
MySQL 學習筆記 共 8 篇
  1. 4
    10人小班的基本資料庫操作
  2. 5
    資料庫延伸操作
  3. 6
    運用 where... in...和 join
  4. 7
    create view
  5. 8
    北風資料庫情境模擬
完整目錄
圖片
  直播研討會
圖片
{{ item.channelVendor }} {{ item.webinarstarted }} |
{{ formatDate(item.duration) }}
直播中

尚未有邦友留言

立即登入留言
iThome鐵人賽
參賽組數
1123 組
團體組數
52 組
累計文章數
23096 篇
完賽人數
656 人
iT+看影片追技術 看影片追技術 看更多
熱門tag 看更多
15th鐵人賽 13th鐵人賽 14th鐵人賽 12th鐵人賽 11th鐵人賽 鐵人賽 2019鐵人賽 2018鐵人賽 javascript 2017鐵人賽 python windows php c# windows server linux css 程式設計 react vue.js
熱門問題
熱門回答
熱門文章
IT邦幫忙